from datetime import datetime
from app.models.mysql_db import db

class HourlyStats(db.Model):
    """每小时环境数据统计模型"""
    __tablename__ = 'hourly_stats'
    
    id = db.Column(db.Integer, primary_key=True)
    device_id = db.Column(db.String(50), nullable=False, index=True)
    hour = db.Column(db.DateTime, nullable=False)
    data_type = db.Column(db.String(20), nullable=False)
    avg_value = db.Column(db.Float)
    max_value = db.Column(db.Float)
    min_value = db.Column(db.Float)
    count = db.Column(db.Integer)
    created_at = db.Column(db.DateTime, default=datetime.now)
    
    # 联合唯一索引，确保每个设备每小时每种数据类型只有一条记录
    __table_args__ = (
        db.UniqueConstraint('device_id', 'hour', 'data_type', name='uix_hourly_stats'),
    )
    
    def __repr__(self):
        return f'<HourlyStats {self.device_id} {self.hour} {self.data_type}>'
    
    @classmethod
    def save_stats(cls, device_id, hour_str, data_type, avg_value, max_value, min_value, count):
        """保存或更新统计数据"""
        try:
            # 将字符串时间转换为datetime对象
            hour = datetime.strptime(hour_str, "%Y-%m-%d %H:00:00")
            
            # 查找是否已存在记录
            existing = cls.query.filter_by(
                device_id=device_id,
                hour=hour,
                data_type=data_type
            ).first()
            
            if existing:
                # 更新现有记录
                existing.avg_value = avg_value
                existing.max_value = max_value
                existing.min_value = min_value
                existing.count = count
                existing.created_at = datetime.now()
            else:
                # 创建新记录
                new_stat = cls(
                    device_id=device_id,
                    hour=hour,
                    data_type=data_type,
                    avg_value=avg_value,
                    max_value=max_value,
                    min_value=min_value,
                    count=count
                )
                db.session.add(new_stat)
            
            db.session.commit()
            return True
        except Exception as e:
            db.session.rollback()
            print(f"保存统计数据失败: {str(e)}")
            return False